<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>MyBatis 动态 SQL | 我的笔记</title>
    <meta name="generator" content="VuePress 1.8.2">
    <link rel="icon" href="/notebook/favicon.ico">
    <script data-ad-client="ca-pub-4147143076931995" async="true" src="/notebook//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
    <meta name="description" content="方便阅读和寻找">
    
    <link rel="preload" href="/notebook/assets/css/0.styles.cee65b40.css" as="style"><link rel="preload" href="/notebook/assets/js/app.400f01da.js" as="script"><link rel="preload" href="/notebook/assets/js/2.ffba27f2.js" as="script"><link rel="preload" href="/notebook/assets/js/22.bfedf3cf.js" as="script"><link rel="prefetch" href="/notebook/assets/js/10.9da57264.js"><link rel="prefetch" href="/notebook/assets/js/100.8aa79850.js"><link rel="prefetch" href="/notebook/assets/js/101.f8819976.js"><link rel="prefetch" href="/notebook/assets/js/102.7168e4aa.js"><link rel="prefetch" href="/notebook/assets/js/103.e1f049f7.js"><link rel="prefetch" href="/notebook/assets/js/104.0ada8567.js"><link rel="prefetch" href="/notebook/assets/js/105.77e95b95.js"><link rel="prefetch" href="/notebook/assets/js/106.9071202a.js"><link rel="prefetch" href="/notebook/assets/js/107.a0a63241.js"><link rel="prefetch" href="/notebook/assets/js/108.93ccf0bf.js"><link rel="prefetch" href="/notebook/assets/js/109.57caff98.js"><link rel="prefetch" href="/notebook/assets/js/11.d9d769c3.js"><link rel="prefetch" href="/notebook/assets/js/110.6aa16c3f.js"><link rel="prefetch" href="/notebook/assets/js/111.293e18e5.js"><link rel="prefetch" href="/notebook/assets/js/112.7f593e53.js"><link rel="prefetch" href="/notebook/assets/js/113.e03d7fe0.js"><link rel="prefetch" href="/notebook/assets/js/114.4fd73421.js"><link rel="prefetch" href="/notebook/assets/js/115.8dc5910d.js"><link rel="prefetch" href="/notebook/assets/js/116.fc5215b7.js"><link rel="prefetch" href="/notebook/assets/js/117.851bae37.js"><link rel="prefetch" href="/notebook/assets/js/118.03cb2186.js"><link rel="prefetch" href="/notebook/assets/js/119.17f93b7e.js"><link rel="prefetch" href="/notebook/assets/js/12.125d7a59.js"><link rel="prefetch" href="/notebook/assets/js/120.4ea767fb.js"><link rel="prefetch" href="/notebook/assets/js/121.483ad579.js"><link rel="prefetch" href="/notebook/assets/js/122.17d2596f.js"><link rel="prefetch" href="/notebook/assets/js/123.56e20e4e.js"><link rel="prefetch" href="/notebook/assets/js/124.2ec822f9.js"><link rel="prefetch" href="/notebook/assets/js/125.bccb84ed.js"><link rel="prefetch" href="/notebook/assets/js/126.e38a7466.js"><link rel="prefetch" href="/notebook/assets/js/127.43962ebd.js"><link rel="prefetch" href="/notebook/assets/js/128.23f8246b.js"><link rel="prefetch" href="/notebook/assets/js/129.e718e26c.js"><link rel="prefetch" href="/notebook/assets/js/13.f56b83ad.js"><link rel="prefetch" href="/notebook/assets/js/130.a51fe94d.js"><link rel="prefetch" href="/notebook/assets/js/131.64fb54a3.js"><link rel="prefetch" href="/notebook/assets/js/132.badc2a1a.js"><link rel="prefetch" href="/notebook/assets/js/133.72f12c19.js"><link rel="prefetch" href="/notebook/assets/js/134.0b1b2dc0.js"><link rel="prefetch" href="/notebook/assets/js/135.be2d740c.js"><link rel="prefetch" href="/notebook/assets/js/136.c3dc9ba1.js"><link rel="prefetch" href="/notebook/assets/js/137.784510e4.js"><link rel="prefetch" href="/notebook/assets/js/138.ff844e48.js"><link rel="prefetch" href="/notebook/assets/js/139.1ac43f97.js"><link rel="prefetch" href="/notebook/assets/js/14.3ed225c4.js"><link rel="prefetch" href="/notebook/assets/js/140.4c427bf3.js"><link rel="prefetch" href="/notebook/assets/js/141.459f2cb4.js"><link rel="prefetch" href="/notebook/assets/js/142.e69e2b00.js"><link rel="prefetch" href="/notebook/assets/js/143.b9cd433a.js"><link rel="prefetch" href="/notebook/assets/js/144.c12cb09a.js"><link rel="prefetch" href="/notebook/assets/js/145.43e25cc4.js"><link rel="prefetch" href="/notebook/assets/js/146.0963c075.js"><link rel="prefetch" href="/notebook/assets/js/147.37f2e690.js"><link rel="prefetch" href="/notebook/assets/js/148.631fbe69.js"><link rel="prefetch" href="/notebook/assets/js/149.a22c5490.js"><link rel="prefetch" href="/notebook/assets/js/15.10a06d24.js"><link rel="prefetch" href="/notebook/assets/js/150.e6265d54.js"><link rel="prefetch" href="/notebook/assets/js/151.aefdb9fd.js"><link rel="prefetch" href="/notebook/assets/js/152.a8c722bd.js"><link rel="prefetch" href="/notebook/assets/js/153.ec2b1513.js"><link rel="prefetch" href="/notebook/assets/js/154.4fdc765f.js"><link rel="prefetch" href="/notebook/assets/js/155.de537bd1.js"><link rel="prefetch" href="/notebook/assets/js/156.7f87247c.js"><link rel="prefetch" href="/notebook/assets/js/157.f51afdd1.js"><link rel="prefetch" href="/notebook/assets/js/158.247f3f23.js"><link rel="prefetch" href="/notebook/assets/js/159.2342ec68.js"><link rel="prefetch" href="/notebook/assets/js/16.bc052b7f.js"><link rel="prefetch" href="/notebook/assets/js/160.98e33fe9.js"><link rel="prefetch" href="/notebook/assets/js/161.b8389795.js"><link rel="prefetch" href="/notebook/assets/js/162.5809ad21.js"><link rel="prefetch" href="/notebook/assets/js/163.6d40855f.js"><link rel="prefetch" href="/notebook/assets/js/164.cd5e8c28.js"><link rel="prefetch" href="/notebook/assets/js/165.c409d97c.js"><link rel="prefetch" href="/notebook/assets/js/166.75cd78fe.js"><link rel="prefetch" href="/notebook/assets/js/167.37552374.js"><link rel="prefetch" href="/notebook/assets/js/168.d4ab1d56.js"><link rel="prefetch" href="/notebook/assets/js/169.39f3c518.js"><link rel="prefetch" href="/notebook/assets/js/17.2a8873d5.js"><link rel="prefetch" href="/notebook/assets/js/170.7993242d.js"><link rel="prefetch" href="/notebook/assets/js/171.09f40b60.js"><link rel="prefetch" href="/notebook/assets/js/172.79168981.js"><link rel="prefetch" href="/notebook/assets/js/173.a75cd141.js"><link rel="prefetch" href="/notebook/assets/js/174.bd510afd.js"><link rel="prefetch" href="/notebook/assets/js/175.c3f8ba94.js"><link rel="prefetch" href="/notebook/assets/js/176.dd9cbd79.js"><link rel="prefetch" href="/notebook/assets/js/177.f53090f5.js"><link rel="prefetch" href="/notebook/assets/js/178.bcda53b0.js"><link rel="prefetch" href="/notebook/assets/js/179.2d510aa5.js"><link rel="prefetch" href="/notebook/assets/js/18.2d0a0d6d.js"><link rel="prefetch" href="/notebook/assets/js/180.6928e4f9.js"><link rel="prefetch" href="/notebook/assets/js/181.6cc94652.js"><link rel="prefetch" href="/notebook/assets/js/182.c7d6fde2.js"><link rel="prefetch" href="/notebook/assets/js/183.1a7eaa90.js"><link rel="prefetch" href="/notebook/assets/js/184.5d52b650.js"><link rel="prefetch" href="/notebook/assets/js/185.2f8eddcd.js"><link rel="prefetch" href="/notebook/assets/js/186.2615bcf2.js"><link rel="prefetch" href="/notebook/assets/js/187.ffb3009d.js"><link rel="prefetch" href="/notebook/assets/js/188.deef127e.js"><link rel="prefetch" href="/notebook/assets/js/189.51b1958b.js"><link rel="prefetch" href="/notebook/assets/js/19.482e846f.js"><link rel="prefetch" href="/notebook/assets/js/190.a5e85724.js"><link rel="prefetch" href="/notebook/assets/js/191.fd2dfc70.js"><link rel="prefetch" href="/notebook/assets/js/192.5cb3b141.js"><link rel="prefetch" href="/notebook/assets/js/193.3a6173b0.js"><link rel="prefetch" href="/notebook/assets/js/194.2b937e4b.js"><link rel="prefetch" href="/notebook/assets/js/195.71b1b3e2.js"><link rel="prefetch" href="/notebook/assets/js/196.d8d64ba1.js"><link rel="prefetch" href="/notebook/assets/js/197.fbea3131.js"><link rel="prefetch" href="/notebook/assets/js/198.25e90057.js"><link rel="prefetch" href="/notebook/assets/js/199.5bef52d0.js"><link rel="prefetch" href="/notebook/assets/js/20.3869e9c1.js"><link rel="prefetch" href="/notebook/assets/js/200.839b8484.js"><link rel="prefetch" href="/notebook/assets/js/201.dcc87a43.js"><link rel="prefetch" href="/notebook/assets/js/202.83abe52e.js"><link rel="prefetch" href="/notebook/assets/js/203.c8c886fe.js"><link rel="prefetch" href="/notebook/assets/js/204.7d91a0aa.js"><link rel="prefetch" href="/notebook/assets/js/205.dd934d84.js"><link rel="prefetch" href="/notebook/assets/js/206.ae9d7602.js"><link rel="prefetch" href="/notebook/assets/js/207.f5ac5260.js"><link rel="prefetch" href="/notebook/assets/js/208.203ba066.js"><link rel="prefetch" href="/notebook/assets/js/209.202991be.js"><link rel="prefetch" href="/notebook/assets/js/21.95b2d828.js"><link rel="prefetch" href="/notebook/assets/js/210.3e7c6db0.js"><link rel="prefetch" href="/notebook/assets/js/211.d231f4d5.js"><link rel="prefetch" href="/notebook/assets/js/212.74210f2c.js"><link rel="prefetch" href="/notebook/assets/js/213.ca497e10.js"><link rel="prefetch" href="/notebook/assets/js/214.256f2f8d.js"><link rel="prefetch" href="/notebook/assets/js/215.e5a91195.js"><link rel="prefetch" href="/notebook/assets/js/23.07416482.js"><link rel="prefetch" href="/notebook/assets/js/24.54aaf5dd.js"><link rel="prefetch" href="/notebook/assets/js/25.5fddfc47.js"><link rel="prefetch" href="/notebook/assets/js/26.3beace9a.js"><link rel="prefetch" href="/notebook/assets/js/27.8166fc8b.js"><link rel="prefetch" href="/notebook/assets/js/28.45eec4fe.js"><link rel="prefetch" href="/notebook/assets/js/29.7d2c15a3.js"><link rel="prefetch" href="/notebook/assets/js/3.58b1a014.js"><link rel="prefetch" href="/notebook/assets/js/30.a7841312.js"><link rel="prefetch" href="/notebook/assets/js/31.dc9675e8.js"><link rel="prefetch" href="/notebook/assets/js/32.93c6c35d.js"><link rel="prefetch" href="/notebook/assets/js/33.9a64b2f6.js"><link rel="prefetch" href="/notebook/assets/js/34.0c220f3c.js"><link rel="prefetch" href="/notebook/assets/js/35.9f407421.js"><link rel="prefetch" href="/notebook/assets/js/36.ee8ac781.js"><link rel="prefetch" href="/notebook/assets/js/37.569079d1.js"><link rel="prefetch" href="/notebook/assets/js/38.56b32d83.js"><link rel="prefetch" href="/notebook/assets/js/39.6ea9c955.js"><link rel="prefetch" href="/notebook/assets/js/4.5042ba18.js"><link rel="prefetch" href="/notebook/assets/js/40.5bdebc85.js"><link rel="prefetch" href="/notebook/assets/js/41.8607bd5a.js"><link rel="prefetch" href="/notebook/assets/js/42.2063b1d0.js"><link rel="prefetch" href="/notebook/assets/js/43.ff59782c.js"><link rel="prefetch" href="/notebook/assets/js/44.c40eaded.js"><link rel="prefetch" href="/notebook/assets/js/45.bc61bb49.js"><link rel="prefetch" href="/notebook/assets/js/46.e9ea5687.js"><link rel="prefetch" href="/notebook/assets/js/47.a9626a0e.js"><link rel="prefetch" href="/notebook/assets/js/48.9bf986fe.js"><link rel="prefetch" href="/notebook/assets/js/49.dd90158a.js"><link rel="prefetch" href="/notebook/assets/js/5.b9679d2a.js"><link rel="prefetch" href="/notebook/assets/js/50.a9546c5a.js"><link rel="prefetch" href="/notebook/assets/js/51.dcc646ec.js"><link rel="prefetch" href="/notebook/assets/js/52.111a35ff.js"><link rel="prefetch" href="/notebook/assets/js/53.19719081.js"><link rel="prefetch" href="/notebook/assets/js/54.543f990e.js"><link rel="prefetch" href="/notebook/assets/js/55.02863756.js"><link rel="prefetch" href="/notebook/assets/js/56.262a1288.js"><link rel="prefetch" href="/notebook/assets/js/57.a80801ee.js"><link rel="prefetch" href="/notebook/assets/js/58.38d623bc.js"><link rel="prefetch" href="/notebook/assets/js/59.07a6b6b6.js"><link rel="prefetch" href="/notebook/assets/js/6.77dfe150.js"><link rel="prefetch" href="/notebook/assets/js/60.df11d23d.js"><link rel="prefetch" href="/notebook/assets/js/61.764a5b2e.js"><link rel="prefetch" href="/notebook/assets/js/62.28916805.js"><link rel="prefetch" href="/notebook/assets/js/63.f54fb2ba.js"><link rel="prefetch" href="/notebook/assets/js/64.73bb9e27.js"><link rel="prefetch" href="/notebook/assets/js/65.8e45d2c8.js"><link rel="prefetch" href="/notebook/assets/js/66.6cabc639.js"><link rel="prefetch" href="/notebook/assets/js/67.61f4e99c.js"><link rel="prefetch" href="/notebook/assets/js/68.fec9c74e.js"><link rel="prefetch" href="/notebook/assets/js/69.b530b731.js"><link rel="prefetch" href="/notebook/assets/js/7.bac04506.js"><link rel="prefetch" href="/notebook/assets/js/70.e2902def.js"><link rel="prefetch" href="/notebook/assets/js/71.09df6e0a.js"><link rel="prefetch" href="/notebook/assets/js/72.5c611ac8.js"><link rel="prefetch" href="/notebook/assets/js/73.66c43b39.js"><link rel="prefetch" href="/notebook/assets/js/74.243e6a2f.js"><link rel="prefetch" href="/notebook/assets/js/75.de524cab.js"><link rel="prefetch" href="/notebook/assets/js/76.750c2fc0.js"><link rel="prefetch" href="/notebook/assets/js/77.fa7ddd5d.js"><link rel="prefetch" href="/notebook/assets/js/78.192816fd.js"><link rel="prefetch" href="/notebook/assets/js/79.a3f3fcd9.js"><link rel="prefetch" href="/notebook/assets/js/8.48754361.js"><link rel="prefetch" href="/notebook/assets/js/80.c3fd6acb.js"><link rel="prefetch" href="/notebook/assets/js/81.7ba4627a.js"><link rel="prefetch" href="/notebook/assets/js/82.e355d704.js"><link rel="prefetch" href="/notebook/assets/js/83.2579aea2.js"><link rel="prefetch" href="/notebook/assets/js/84.1f72cccf.js"><link rel="prefetch" href="/notebook/assets/js/85.7874726d.js"><link rel="prefetch" href="/notebook/assets/js/86.a863f0c3.js"><link rel="prefetch" href="/notebook/assets/js/87.c8c7690e.js"><link rel="prefetch" href="/notebook/assets/js/88.5d7963f9.js"><link rel="prefetch" href="/notebook/assets/js/89.9391f30b.js"><link rel="prefetch" href="/notebook/assets/js/9.c2fb6f2c.js"><link rel="prefetch" href="/notebook/assets/js/90.76bedd2e.js"><link rel="prefetch" href="/notebook/assets/js/91.e345e87a.js"><link rel="prefetch" href="/notebook/assets/js/92.175f428e.js"><link rel="prefetch" href="/notebook/assets/js/93.f45b4417.js"><link rel="prefetch" href="/notebook/assets/js/94.e3f459de.js"><link rel="prefetch" href="/notebook/assets/js/95.1632f845.js"><link rel="prefetch" href="/notebook/assets/js/96.4d0a41d5.js"><link rel="prefetch" href="/notebook/assets/js/97.39bd1831.js"><link rel="prefetch" href="/notebook/assets/js/98.54282751.js"><link rel="prefetch" href="/notebook/assets/js/99.b60593a3.js">
    <link rel="stylesheet" href="/notebook/assets/css/0.styles.cee65b40.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container"><header class="navbar"><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/notebook/" class="home-link router-link-active"><!----> <span class="site-name">我的笔记</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/notebook/guide/" class="nav-link">
  指南
</a></div> <!----></nav></div></header> <div class="sidebar-mask"></div> <aside class="sidebar"><nav class="nav-links"><div class="nav-item"><a href="/notebook/guide/" class="nav-link">
  指南
</a></div> <!----></nav>  <ul class="sidebar-links"><li><section class="sidebar-group depth-0"><p class="sidebar-heading open"><span>MyBatis</span> <!----></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/notebook/mybatis/" aria-current="page" class="sidebar-link">MyBatis 简介</a></li><li><a href="/notebook/mybatis/Druid 简介.html" class="sidebar-link">Druid 简介</a></li><li><a href="/notebook/mybatis/Spring 整合 Druid.html" class="sidebar-link">Spring 整合 Druid</a></li><li><a href="/notebook/mybatis/Spring 整合 MyBatis.html" class="sidebar-link">Spring 整合 MyBatis</a></li><li><a href="/notebook/mybatis/小知识-utf8 与 utf8mb4 字符集.html" class="sidebar-link">小知识-utf8 与 utf8mb4 字符集</a></li><li><a href="/notebook/mybatis/第一个 MyBatis 对象关系映射.html" class="sidebar-link">第一个 MyBatis 对象关系映射</a></li><li><a href="/notebook/mybatis/MyBatis 单表 CRUD 操作.html" class="sidebar-link">MyBatis 单表 CRUD 操作</a></li><li><a href="/notebook/mybatis/MyBatis 动态 SQL.html" class="active sidebar-link">MyBatis 动态 SQL</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/notebook/mybatis/MyBatis 动态 SQL.html#注意事项" class="sidebar-link">注意事项</a></li><li class="sidebar-sub-header"><a href="/notebook/mybatis/MyBatis 动态 SQL.html#if-标签" class="sidebar-link">if 标签</a></li><li class="sidebar-sub-header"><a href="/notebook/mybatis/MyBatis 动态 SQL.html#where-标签" class="sidebar-link">where 标签</a></li><li class="sidebar-sub-header"><a href="/notebook/mybatis/MyBatis 动态 SQL.html#choose-标签" class="sidebar-link">choose 标签</a></li><li class="sidebar-sub-header"><a href="/notebook/mybatis/MyBatis 动态 SQL.html#foreach-标签-遍历数组" class="sidebar-link">foreach 标签-遍历数组</a></li><li class="sidebar-sub-header"><a href="/notebook/mybatis/MyBatis 动态 SQL.html#foreach-标签-遍历集合" class="sidebar-link">foreach 标签-遍历集合</a></li><li class="sidebar-sub-header"><a href="/notebook/mybatis/MyBatis 动态 SQL.html#sql-标签" class="sidebar-link">sql 标签</a></li></ul></li></ul></section></li></ul> </aside> <main class="page"> <div class="theme-default-content content__default"><h1 id="mybatis-动态-sql"><a href="#mybatis-动态-sql" class="header-anchor">#</a> MyBatis 动态 SQL</h1> <p>动态 SQL，主要用于解决查询条件不确定的情况：在程序运行期间，根据用户提交的查询条件进行查询。提交的查询条件不同，执行的 SQL 语句不同。若将每种可能的情况均逐一列出，对所有条件进行排列组合，将会出现大量的 SQL 语句。此时，可使用动态 SQL 来解决这样的问题。</p> <p><img src="" alt="img"></p> <p>动态 SQL，即通过 MyBatis 提供的各种标签对条件作出判断以实现动态拼接 SQL 语句。</p> <p>这里的条件判断使用的表达式为 OGNL 表达式。常用的动态 SQL 标签有 <code>&lt;if&gt;</code>、<code>&lt;where&gt;</code>、<code>&lt;choose&gt;</code>、<code>&lt;foreach&gt;</code> 等。</p> <h2 id="注意事项"><a href="#注意事项" class="header-anchor">#</a> 注意事项</h2> <p>在 mapper 的动态 SQL 中若出现大于号（<code>&gt;</code>）、小于号（<code>&lt;</code>）、大于等于号（<code>&gt;=</code>），小于等于号（<code>&lt;=</code>）等符号，最好将其转换为实体符号。否则，XML 可能会出现解析出错问题。</p> <p>特别是对于小于号（<code>&lt;</code>），在 XML 中是绝对不能出现的。否则，一定出错。</p> <p><img src="/notebook/assets/img/Lusifer1514409933.6bf217b8.png" alt="img"></p> <h2 id="if-标签"><a href="#if-标签" class="header-anchor">#</a> if 标签</h2> <p>对于该标签的执行，当 test 的值为 true 时，会将其包含的 SQL 片断拼接到其所在的 SQL 语句中。</p> <p>本例实现的功能是：查询出满足用户提交查询条件的所有学生。用户提交的查询条件可以包含一个姓名的模糊查询，同时还可以包含一个年龄的下限。当然，用户在提交表单时可能两个条件均做出了设定，也可能两个条件均不做设定，也可以只做其中一项设定。</p> <p>这引发的问题是，查询条件不确定，查询条件依赖于用户提交的内容。此时，就可使用动态 SQL 语句，根据用户提交内容对将要执行的 SQL 进行拼接。</p> <h3 id="定义映射文件"><a href="#定义映射文件" class="header-anchor">#</a> 定义映射文件</h3> <p>为了解决两个条件均未做设定的情况，在 <code>where</code> 后添加了一个“<code>1=1</code>”的条件。这样就不至于两个条件均未设定而出现只剩下一个 <code>where</code>，而没有任何可拼接的条件的不完整 SQL 语句。</p> <div class="language-text extra-class"><pre class="language-text"><code>&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot; ?&gt;
&lt;!DOCTYPE mapper PUBLIC &quot;-//mybatis.org//DTD Mapper 3.0//EN&quot; &quot;http://mybatis.org/dtd/mybatis-3-mapper.dtd&quot;&gt;
&lt;mapper namespace=&quot;com.lusifer.mybatis.dao.DynamicStudentDao&quot;&gt;
    &lt;!-- if --&gt;
    &lt;select id=&quot;selectByIf&quot; resultType=&quot;com.lusifer.mybatis.entity.Student&quot;&gt;
        SELECT
            id,
            name,
            age,
            score
        FROM
            student
        WHERE 1 = 1
        &lt;if test=&quot;name != null and name != ''&quot;&gt;
            AND name LIKE concat('%', #{name}, '%')
        &lt;/if&gt;
        &lt;if test=&quot;age != null and age &gt; 0&quot;&gt;
            AND age &gt; #{age}
        &lt;/if&gt;
    &lt;/select&gt;
&lt;/mapper&gt;
</code></pre></div><h2 id="where-标签"><a href="#where-标签" class="header-anchor">#</a> where 标签</h2> <p><code>&lt;if/&gt;</code> 标签的中存在一个比较麻烦的地方：需要在 <code>where</code> 后手工添加 <code>1=1</code> 的子句。因为，若 <code>where</code> 后的所有 <code>&lt;if/&gt;</code> 条件均为 <code>false</code>，而 <code>where</code> 后若又没有 <code>1=1</code> 子句，则 SQL 中就会只剩下一个空的 <code>where</code>，SQL 出错。所以，在 <code>where</code> 后，需要添加永为真子句 <code>1=1</code>，以防止这种情况的发生。但当数据量很大时，会严重影响查询效率。</p> <h3 id="定义映射文件-2"><a href="#定义映射文件-2" class="header-anchor">#</a> 定义映射文件</h3> <div class="language-text extra-class"><pre class="language-text"><code>&lt;!-- where--&gt;
&lt;select id=&quot;selectByWhere&quot; resultType=&quot;com.lusifer.mybatis.entity.Student&quot;&gt;
    SELECT
        id,
        name,
        age,
        score
    FROM
      student
    &lt;where&gt;
        &lt;if test=&quot;name != null and name != ''&quot;&gt;
            AND name LIKE concat('%', #{name}, '%')
        &lt;/if&gt;
        &lt;if test=&quot;age != null and age &gt; 0&quot;&gt;
            AND age &gt; #{age}
        &lt;/if&gt;
    &lt;/where&gt;
&lt;/select&gt;
</code></pre></div><h2 id="choose-标签"><a href="#choose-标签" class="header-anchor">#</a> choose 标签</h2> <p>该标签中只可以包含 <code>&lt;when/&gt;</code> <code>&lt;otherwise/&gt;</code>，可以包含多个 <code>&lt;when/&gt;</code> 与一个 <code>&lt;otherwise/&gt;</code>。它们联合使用，完成 Java 中的开关语句 switch..case 功能。</p> <p>本例要完成的需求是，若姓名不空，则按照姓名查询；若姓名为空，则按照年龄查询；若没有查询条件，则没有查询结果。</p> <h3 id="定义映射文件-3"><a href="#定义映射文件-3" class="header-anchor">#</a> 定义映射文件</h3> <div class="language-text extra-class"><pre class="language-text"><code>&lt;!-- choose --&gt;
&lt;select id=&quot;selectByChoose&quot; resultType=&quot;com.lusifer.mybatis.entity.Student&quot;&gt;
    SELECT
        id,
        name,
        age,
        score
    FROM
      student
    &lt;where&gt;
        &lt;choose&gt;
            &lt;when test=&quot;name != null and name != ''&quot;&gt;
                AND name LIKE concat('%', #{name}, '%')
            &lt;/when&gt;
            &lt;when test=&quot;age != null and age &gt; 0&quot;&gt;
                AND age &gt; #{age}
            &lt;/when&gt;
            &lt;otherwise&gt;
                AND 1 != 1
            &lt;/otherwise&gt;
        &lt;/choose&gt;
    &lt;/where&gt;
&lt;/select&gt;
</code></pre></div><h2 id="foreach-标签-遍历数组"><a href="#foreach-标签-遍历数组" class="header-anchor">#</a> foreach 标签-遍历数组</h2> <p><code>&lt;foreach/&gt;</code> 标签用于实现对于数组与集合的遍历。对其使用，需要注意：</p> <ul><li><code>collection</code> 表示要遍历的集合类型，这里是数组，即 array。</li> <li><code>open</code>、<code>close</code>、<code>separator</code> 为对遍历内容的 SQL 拼接。</li></ul> <p>本例实现的需求是，查询出 id 为 2 与 4 的学生信息。</p> <h3 id="定义映射文件-4"><a href="#定义映射文件-4" class="header-anchor">#</a> 定义映射文件</h3> <p>动态 SQL 的判断中使用的都是 OGNL 表达式。OGNL 表达式中的数组使用 <code>array</code> 表示，数组长度使用 <code>array.length</code> 表示。</p> <p><img src="/notebook/assets/img/Lusifer1514413085.3c3e4dab.png" alt="img"></p> <div class="language-text extra-class"><pre class="language-text"><code>&lt;!-- foreach --&gt;
&lt;select id=&quot;selectByForeach&quot; resultType=&quot;com.lusifer.mybatis.entity.Student&quot;&gt;
    &lt;!-- select * from student where id in (2, 4) --&gt;
    SELECT
        id,
        name,
        age,
        score
    FROM
      student
    &lt;if test=&quot;array != null and array.length &gt; 0&quot;&gt;
        WHERE id IN
        &lt;foreach collection=&quot;array&quot; open=&quot;(&quot; close=&quot;)&quot; item=&quot;id&quot; separator=&quot;,&quot;&gt;
            #{id}
        &lt;/foreach&gt;
    &lt;/if&gt;
&lt;/select&gt;
</code></pre></div><h2 id="foreach-标签-遍历集合"><a href="#foreach-标签-遍历集合" class="header-anchor">#</a> foreach 标签-遍历集合</h2> <p>遍历集合的方式与遍历数组的方式相同，只不过是将 <code>array</code> 替换成了 <code>list</code></p> <h3 id="遍历泛型为基本类型的-list"><a href="#遍历泛型为基本类型的-list" class="header-anchor">#</a> 遍历泛型为基本类型的 List</h3> <h4 id="定义-dao-接口"><a href="#定义-dao-接口" class="header-anchor">#</a> 定义 DAO 接口</h4> <div class="language-text extra-class"><pre class="language-text"><code>/**
 * 使用 foreach 标签以 list 基本类型的形式查询
 * @param ids
 * @return
 */
public List&lt;Student&gt; selectByForeachWithListBase(List&lt;Long&gt; ids);
</code></pre></div><h4 id="定义映射文件-5"><a href="#定义映射文件-5" class="header-anchor">#</a> 定义映射文件</h4> <div class="language-text extra-class"><pre class="language-text"><code>&lt;!-- foreach --&gt;
&lt;select id=&quot;selectByForeachWithListBase&quot; resultType=&quot;com.lusifer.mybatis.entity.Student&quot;&gt;
    &lt;!-- select * from student where id in (2, 4) --&gt;
    SELECT
        id,
        name,
        age,
        score
    FROM
      student
    &lt;if test=&quot;list != null and list.size &gt; 0&quot;&gt;
        WHERE id IN
        &lt;foreach collection=&quot;list&quot; open=&quot;(&quot; close=&quot;)&quot; item=&quot;id&quot; separator=&quot;,&quot;&gt;
            #{id}
        &lt;/foreach&gt;
    &lt;/if&gt;
&lt;/select&gt;
</code></pre></div><h3 id="遍历泛型为自定义类型的-list"><a href="#遍历泛型为自定义类型的-list" class="header-anchor">#</a> 遍历泛型为自定义类型的 List</h3> <h4 id="定义-dao-接口-2"><a href="#定义-dao-接口-2" class="header-anchor">#</a> 定义 DAO 接口</h4> <div class="language-text extra-class"><pre class="language-text"><code>/**
 * 使用 foreach 标签以 list 自定义类型的形式查询
 * @param students
 * @return
 */
public List&lt;Student&gt; selectByForeachWithListCustom(List&lt;Student&gt; students);
</code></pre></div><h4 id="定义映射文件-6"><a href="#定义映射文件-6" class="header-anchor">#</a> 定义映射文件</h4> <div class="language-text extra-class"><pre class="language-text"><code>&lt;!-- foreach --&gt;
&lt;select id=&quot;selectByForeachWithListCustom&quot; resultType=&quot;com.lusifer.mybatis.entity.Student&quot;&gt;
    &lt;!-- select * from student where id in (2, 4) --&gt;
    SELECT
        id,
        name,
        age,
        score
    FROM
      student
    &lt;if test=&quot;list != null and list.size &gt; 0&quot;&gt;
        WHERE id IN
        &lt;foreach collection=&quot;list&quot; open=&quot;(&quot; close=&quot;)&quot; item=&quot;student&quot; separator=&quot;,&quot;&gt;
            #{student.id}
        &lt;/foreach&gt;
    &lt;/if&gt;
&lt;/select&gt;
</code></pre></div><h2 id="sql-标签"><a href="#sql-标签" class="header-anchor">#</a> sql 标签</h2> <p><code>&lt;sql/&gt;</code> 标签用于定义 SQL 片断，以便其它 SQL 标签复用。而其它标签使用该 SQL 片断， 需要使用 <code>&lt;include/&gt;</code> 子标签。该 <code>&lt;sql/&gt;</code> 标签可以定义 SQL 语句中的任何部分，所以 <code>&lt;include/&gt;</code> 子标签可以放在动态 SQL 的任何位置。</p> <h3 id="修改映射文件"><a href="#修改映射文件" class="header-anchor">#</a> 修改映射文件</h3> <div class="language-text extra-class"><pre class="language-text"><code>&lt;sql id=&quot;select&quot;&gt;
    SELECT
        id,
        name,
        age,
        score
    FROM
      student
&lt;/sql&gt;
</code></pre></div><div class="language-text extra-class"><pre class="language-text"><code>&lt;!-- foreach --&gt;
&lt;select id=&quot;selectByForeachWithListCustom&quot; resultType=&quot;com.lusifer.mybatis.entity.Student&quot;&gt;
    &lt;!-- select * from student where id in (2, 4) --&gt;
    &lt;include refid=&quot;select&quot; /&gt;

    &lt;if test=&quot;list != null and list.size &gt; 0&quot;&gt;
        WHERE id IN
        &lt;foreach collection=&quot;list&quot; open=&quot;(&quot; close=&quot;)&quot; item=&quot;student&quot; separator=&quot;,&quot;&gt;
            #{student.id}
        &lt;/foreach&gt;
    &lt;/if&gt;
&lt;/select&gt;
</code></pre></div><p><img src="/notebook/assets/img/Lusifer1514414809.407a953d.png" alt="img"></p></div> <footer class="page-edit"><!----> <div class="last-updated"><span class="prefix">上次更新:</span> <span class="time">2021/4/15 上午12:33:56</span></div></footer> <div class="page-nav"><p class="inner"><span class="prev">
      ←
      <a href="/notebook/mybatis/MyBatis 单表 CRUD 操作.html" class="prev">
        MyBatis 单表 CRUD 操作
      </a></span> <!----></p></div> </main></div><div class="global-ui"><!----></div></div>
    <script src="/notebook/assets/js/app.400f01da.js" defer></script><script src="/notebook/assets/js/2.ffba27f2.js" defer></script><script src="/notebook/assets/js/22.bfedf3cf.js" defer></script>
  </body>
</html>
